﻿# -*- coding:UTF-8 -*-

import os

from docx import Document
from docx.enum.style import WD_STYLE_TYPE
import xlrd #引入excel读取模块

from collections import OrderedDict
import json
import io

from reportlab.lib.units import inch #导入单位英寸
import time

from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.ttfonts import TTFont
from reportlab.platypus import Paragraph, SimpleDocTemplate, Table, TableStyle
from reportlab.lib.units import inch
from reportlab.lib import colors
import math

import xport
import sys

class ConvertExcel:
    __rowNum = 0
    __colNum = 0
    def __init__(self, data_path, sheet, data_save):
       self.sheet = sheet
       self.data_path = data_path
       self.data_save = data_save

    def __check(self):
        data = xlrd.open_workbook(self.data_path)#读取数据
        if self.sheet not in data.sheet_names():
            exit("无此表")

        sheet_data = data.sheet_by_name(self.sheet)

        self.__rowNum = sheet_data.nrows
        self.__colNum = sheet_data.ncols
        if self.__rowNum <= 0:
            exit("为空表")
        return sheet_data

    def ExcelToWord(self):
        sheet_data = self.__check()
        document = Document()
        styles = document.styles
        for s in styles:
            if s.type == WD_STYLE_TYPE.TABLE:
                document.add_heading("excel转word")
                table = document.add_table(self.__rowNum, self.__colNum, style=s)
                for i in range(self.__rowNum):
                    heading_cells = table.rows[i].cells
                    rows = sheet_data.row_values(i)
                    for j in range(self.__colNum):
                        heading_cells[j].text = rows[j]
                document.save(self.data_save + self.sheet + '.docx')
                print("恭喜你转换成功！！！")
                exit()

        document.add_heading("excel转word")
        for y in range(self.__rowNum):
            rows = sheet_data.row_values(y)

            data = [str(j) for j in rows] #将里面的数字转为字符串
            document.add_paragraph(" ".join(data))

        document.save(self.data_save + self.sheet + '.docx')
        print("恭喜你转换成功！！！")

    #
    # def WinExcelToPdf(data_path, sheet, data_save):
    #     xlApp = client.Dispatch("Excel.Application")
    #     books = xlApp.Workbooks.Open(data_path)
    #     books.ExportAsFixedFormat(0, data_save + sheet + '.pdf')
    #     OAxlApp.Quit()
    #
    # def WinPptToPdf(data_path, sheet, data_save):
    #     p = client.Dispatch("PowerPoint.Application")
    #     ppt = p.Presentations.Open(data_path, false, false, false)
    #     ppt.ExportAsFixedFormat(data_save + sheet + ".pdf", 2, printRange = None)
    #     p.Quit()


    def ExcelToPdf(self):
        pdfmetrics.registerFont(TTFont('simHei', '/var/www/html/python/fonts/SimHei.ttf'))
        sheet_data = self.__check()
        story = []
        table_data = []
        for y in range(self.__rowNum):
            rows = sheet_data.row_values(y)
            data = [str(j) for j in rows] #将里面的数字转为字符串
            table_data.append(data)

        table_style = [
            ('FONTNAME', (0, 0), (-1, -1), 'simHei'),  # 字体
            ('FONTSIZE', (0, 0), (-1, 0), 6),  # 字体大小
            ('FONTSIZE', (0, 0), (-1, -1), 8),  # 第一行的字体大小
            ('TEXTCOLOR', (0, 0), (-1, -1), colors.darkslategray),  # 设置表格内文字颜色
            ('GRID', (0, 0), (-1, -1), 0.1, colors.slategray),  # 设置表格框线为灰色，线宽为0.1
        ]
        colWidths = [math.ceil((7.8 / len(sheet_data.row_values(1))) * inch) for j in range(len(sheet_data.row_values(0)))]
        try:
            table_table = Table(table_data, colWidths = colWidths, style = table_style)#将数据套到已经设定好的表格样式中，并且设置列宽

            story.append(table_table)#将表格添加到list：story 中

            doc = SimpleDocTemplate(self.data_save + self.sheet + '.pdf')
            doc.build(story)
            print("恭喜你转换成功！！！")
        except IndexError:
                pass

    def ExcelToJson(self):
        sheet_data = self.__check()
        list = []
        title = sheet_data.row_values(0)  # 表头，json文件的key
        for i in range(self.__rowNum):
            row = sheet_data.row_values(i)
            single = OrderedDict()  # 有序字典
            for j in range(len(row)):
                # print("key:{0}, value:{1}".format(title[j], row[j]))
                single[title[j]] = row[j]

            list.append(single)

        j = json.dumps(list)

        with io.open(self.data_save + self.sheet + ".json", "w", encoding = "utf-8") as f:
            f.write(j)
        print("恭喜你转换成功！！！")


    def ExcelToXpt(self, type):
        sheet_data = self.__check()
        list = []

        if type == 1:
            for i in range(self.__rowNum):
                rows = sheet_data.row_values(i)
                rows = [ item.encode('gbk') for item in rows]
                list.append(tuple(rows))

            with io.open(self.data_save + self.sheet + '.xpt', 'wb') as f:
                xport.from_rows(list, f)

        elif type == 2:
            single = OrderedDict()  # 有序字典
            for i in range(self.__colNum):
                cols = sheet_data.col_values(i)
                cols = [ item.encode('gbk') for item in cols]
                cols.pop(0)
                single[sheet_data.row_values(0)[i]] = cols

            with open(self.data_save + self.sheet + '.xpt', 'wb') as f:
                xport.from_columns(single, f)

    def ReaderXpt(self):
        with io.open(data_save + sheet + '.xpt', 'rb') as f:
            for row in xport.Reader(f):
                print (row)

    def ReaderWordToExcel(self):
        docx = document = Document('/home/syy/文档/2019-06-03/22.xlsx')
        print("段落数:" + str(len(docx.paragraphs))) #输出段落数

if __name__ == '__main__':

    data_path = '/home/syy/下载/test.xlsx'
    data_save = '/home/syy/下载/'
    # sheet = input("请输入Sheet名称：")
    excel = ConvertExcel(data_path, 'Sheet1', data_save)
    # excel.ReaderWordToExcel()
    # exit()
    # excel.ExcelToJson()
    excel.ExcelToPdf()
    # excel.ExcelToWord()
    # excel.ReaderXpt()
    # excel.ExcelToXpt(2)
